package cn.com.callback
import groovy.json.JsonSlurper
import groovy.swing.impl.DefaultAction
import groovy.xml.Namespace
import java.text.MessageFormat;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.servlet.RequestDispatcher;

import cn.com.elite.EliteBean;
import java.security.MessageDigest;
import java.sql.ResultSet;
import java.text.SimpleDateFormat;
import java.util.regex.Matcher
import java.util.regex.Pattern

import java.util.HashMap;
import java.util.Map;
import net.sf.json.JSONObject;
import net.sf.json.JSONArray;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.springframework.http.converter.json.MappingJackson2HttpMessageConverter

import com.wisdge.dataservice.Result;
import cn.com.elite.Util;

public class WbWorkDataSummary implements ServiceBackInvoke{
	private static final Log log=LogFactory.getLog(WbWorkDataSummary.class);
	public String doCallBack(EliteBean eliteBean,HttpServletRequest request,HttpServletResponse response) {

		log.debug("工单监控数据汇总开始，处理 wo3number_objective 表数据，汇总当前时间两个月内的数据");

		String WO3GUID=UUID.randomUUID().toString();

		try {
			def v_dateitem="";
			def v_minute=getMinute(new Date());//获取当前分钟
			if(v_minute >=30) {
				v_dateitem=getHour(new Date())*2+1;
				log.debug("得到的结果分钟:"+v_dateitem);
			}
			else
			{
				v_dateitem=getHour(new Date())*2 ;
				log.debug("得到的结果分钟:"+v_dateitem);
			}
			def deleteSQL = "DELETE FROM WO3NUMBER_OBJECTIVE WHERE DATA_TYPE='A'";
			log.debug("清除工单汇总数据A:"+deleteSQL);
			eliteBean.update(deleteSQL,'dataSource');

			def insertSQL = "INSERT INTO WO3NUMBER_OBJECTIVE (WO3GUID,COUNTDATE,DATEITEM_ID,MAP_ID,CREATEDBY_ID,TOTAL,SUCCESS,FAIL,IN_EXPIREDDATE,OUT_EXPIREDDATE,EXPIRED_IN_3D,EXPIRED_IN_10D,EXPIRED_IN_30D,EXPIRED_OUT_30D,custlistname,data_type)  SELECT '"+WO3GUID+"',date_format(o.createddate, '%Y-%m-%d'),'"+v_dateitem+"',o.MAP_Id,o.CREATEDBY,count(o.objective_guid) AS 总数,  count(CASE WHEN o.objectivestatus = 'CLOSE' THEN 1 END ) AS 已结案,  count(CASE WHEN o.objectivestatus = 'FAIL' THEN 1 END ) AS 废弃,  count(CASE WHEN o.objectivestatus = 'OPEN'	AND o.ExpiredDate > now() THEN 1 END) AS 处理中未过期,  count(CASE WHEN o.objectivestatus = 'OPEN' AND o.ExpiredDate <= now() THEN 	1 END ) AS 处理中已过期,  count(CASE WHEN o.objectivestatus = 'OPEN' AND o.ExpiredDate <= now() AND o.ExpiredDate > date_add(now(), INTERVAL - 3 DAY) THEN 	1 END ) AS 过期3天,  count(CASE WHEN o.objectivestatus = 'OPEN' AND o.ExpiredDate <= date_add(now(), INTERVAL - 3 DAY) AND o.ExpiredDate > date_add(now(), INTERVAL - 10 DAY) THEN 	1 END ) AS 过期10天,  count(CASE WHEN o.objectivestatus = 'OPEN' AND o.ExpiredDate <= date_add(now(), INTERVAL - 10 DAY) AND o.ExpiredDate > date_add(now(), INTERVAL - 30 DAY) THEN 	1 END ) AS 过期30天,  count(CASE WHEN o.objectivestatus = 'OPEN' AND o.ExpiredDate <= date_add(now(), INTERVAL - 30 DAY) THEN 	1 END ) AS 过期30天以上,  o.custlistname,'A'    FROM OBJECTIVE o WHERE 1 = 1 AND o.createddate >= STR_TO_DATE(date_format( date_add( 	last_day( 		DATE_ADD(NOW(), INTERVAL - 2 MONTH) 	), 	INTERVAL 1 DAY ), '%Y-%m-%d' ), '%Y-%m-%d %H-%i-%s' )   GROUP BY o.MAP_ID, date_format(o.createddate, '%Y-%m-%d'),o.custlistname,o.CREATEDBY ";
			log.debug("插入工单汇总数据A:"+insertSQL);
			eliteBean.update(insertSQL,'dataSource');

			log.debug("处理 wo3number_task 表数据。。。");

			def deleteSQL1 = "DELETE FROM WO3NUMBER_TASK WHERE DATA_TYPE='A'";
			log.debug("清除工单任务数据A:"+deleteSQL1);
			eliteBean.update(deleteSQL1,'dataSource');

			def insertSQL1 = "INSERT INTO WO3NUMBER_TASK (WO3GUID, COUNTDATE, DATEITEM_ID, MAP_ID, CURSTEP_ID_MAP, HANDLEBY_ID, HANDLEGROUP_ID, TOTAL, IN_EXPIREDDATE, OUT_EXPIREDDATE, STEPEXPIRED_IN_3D, STEPEXPIRED_IN_10D, STEPEXPIRED_IN_30D, STEPEXPIRED_OUT_30D, custlistname, data_type, CREATEDDATE)  SELECT '"+WO3GUID+"',date_format(o.createddate, '%Y-%m-%d'), '"+v_dateitem+"', e.MAP_Id, e.curstep_id_map, e.handleby_id, e.handlegroup_id, count(e.elitetask_guid) AS 总数, 	count( CASE WHEN e.expectendtime > now() THEN 1 END ) AS 工单步骤未过期, 	count( CASE WHEN e.expectendtime <= now() THEN 1 END ) AS 工单步骤已过期, 	count( CASE WHEN e.expectendtime <= now() AND e.expectendtime > date_add(now(), INTERVAL - 3 DAY) THEN 1 END ) AS 工单步骤过期3天, 	count( CASE WHEN e.expectendtime <= date_add(now(), INTERVAL - 3 DAY) AND e.expectendtime > date_add(now(), INTERVAL - 10 DAY) THEN 1 END ) AS 工单步骤过期10天, 	count( CASE WHEN e.expectendtime <= date_add(now(), INTERVAL - 10 DAY) AND e.expectendtime > date_add(now(), INTERVAL - 30 DAY) THEN 1 END ) AS 工单步骤过期30天, 	count( CASE WHEN e.expectendtime <= date_add(now(), INTERVAL - 30 DAY) THEN 	1 END ) AS 工单步骤过期30天以上,     e.custlistname, 'A', date_format(e.createddate,'%Y-%m-%d')	 FROM ELITETASK e LEFT JOIN OBJECTIVE o ON e.objective_guid = o.objective_guid WHERE 1 = 1 AND o.createddate >= STR_TO_DATE(date_format( date_add( last_day( DATE_ADD(NOW(), INTERVAL - 2 MONTH) ), INTERVAL 1 DAY ), '%Y-%m-%d' ), '%Y-%m-%d %H-%i-%s') GROUP BY e.curstep_id_map, e.MAP_ID, date_format(o.createddate, '%Y-%m-%d'),e.custlistname,e.handleby_id,e.handlegroup_id,date_format(e.createddate,'%Y-%m-%d') ";

			log.debug("插入工单任务数据A:"+insertSQL1);
			eliteBean.update(insertSQL1,'dataSource');

			log.debug("开始处理两个月之前的数据，处理 wo3number_objective 表数据，汇总距小于距当前时间两个月之前的数据 。。。");

			def deleteSQL2= "DELETE FROM WO3NUMBER_OBJECTIVE WHERE DATA_TYPE='B'";
			log.debug("清除工单汇总数据B:"+deleteSQL);
			eliteBean.update(deleteSQL2,'dataSource');


			def insertSQL2 = " INSERT INTO WO3NUMBER_OBJECTIVE (COUNTDATE,DATEITEM_ID,MAP_ID,CREATEDBY_ID,TOTAL,SUCCESS,FAIL,IN_EXPIREDDATE,OUT_EXPIREDDATE,EXPIRED_IN_3D,EXPIRED_IN_10D,EXPIRED_IN_30D,EXPIRED_OUT_30D,custlistname,data_type)  SELECT date_format(o.createddate, '%Y-%m-%d'),'"+v_dateitem+"',o.MAP_Id,o.CREATEDBY,count(o.objective_guid) AS 总数,  count(CASE WHEN o.objectivestatus = 'CLOSE' THEN 1 END ) AS 已结案,  count(CASE WHEN o.objectivestatus = 'FAIL' THEN 1 END ) AS 废弃,  count(CASE WHEN o.objectivestatus = 'OPEN'	AND o.ExpiredDate > now() THEN 1 END) AS 处理中未过期,  count(CASE WHEN o.objectivestatus = 'OPEN' AND o.ExpiredDate <= now() THEN 	1 END ) AS 处理中已过期,  count(CASE WHEN o.objectivestatus = 'OPEN' AND o.ExpiredDate <= now() AND o.ExpiredDate > date_add(now(), INTERVAL - 3 DAY) THEN 	1 END ) AS 过期3天,  count(CASE WHEN o.objectivestatus = 'OPEN' AND o.ExpiredDate <= date_add(now(), INTERVAL - 3 DAY) AND o.ExpiredDate > date_add(now(), INTERVAL - 10 DAY) THEN 	1 END ) AS 过期10天,  count(CASE WHEN o.objectivestatus = 'OPEN' AND o.ExpiredDate <= date_add(now(), INTERVAL - 10 DAY) AND o.ExpiredDate > date_add(now(), INTERVAL - 30 DAY) THEN 	1 END ) AS 过期30天,  count(CASE WHEN o.objectivestatus = 'OPEN' AND o.ExpiredDate <= date_add(now(), INTERVAL - 30 DAY) THEN 	1 END ) AS 过期30天以上,  o.custlistname,'B'    FROM OBJECTIVE o WHERE 1 = 1 AND o.createddate < STR_TO_DATE(date_format( date_add( 	last_day( 		DATE_ADD(NOW(), INTERVAL - 2 MONTH) 	), 	INTERVAL 1 DAY ), '%Y-%m-%d' ), '%Y-%m-%d %H-%i-%s' )   GROUP BY o.MAP_ID, date_format(o.createddate, '%Y-%m-%d'),o.custlistname,o.CREATEDBY ";
			log.debug("插入工单汇总数据B:"+insertSQL2);
			eliteBean.update(insertSQL2,'dataSource');

			log.debug("处理 wo3number_task 表数据。。。");

			def deleteSQL3 = "DELETE FROM WO3NUMBER_TASK WHERE DATA_TYPE='B'";
			log.debug("清除工单汇总数据B:"+deleteSQL3);
			eliteBean.update(deleteSQL3,'dataSource');

			def insertSQL3 = "INSERT INTO WO3NUMBER_TASK ( COUNTDATE, DATEITEM_ID, MAP_ID, CURSTEP_ID_MAP, HANDLEBY_ID, HANDLEGROUP_ID, TOTAL, IN_EXPIREDDATE, OUT_EXPIREDDATE, STEPEXPIRED_IN_3D, STEPEXPIRED_IN_10D, STEPEXPIRED_IN_30D, STEPEXPIRED_OUT_30D, custlistname, data_type, CREATEDDATE)  SELECT date_format(o.createddate, '%Y-%m-%d'), '"+v_dateitem+"', e.MAP_Id, e.curstep_id_map, e.handleby_id, e.handlegroup_id, count(e.elitetask_guid) AS 总数, 	count( CASE WHEN e.expectendtime > now() THEN 1 END ) AS 工单步骤未过期, 	count( CASE WHEN e.expectendtime <= now() THEN 1 END ) AS 工单步骤已过期, 	count( CASE WHEN e.expectendtime <= now() AND e.expectendtime > date_add(now(), INTERVAL - 3 DAY) THEN 1 END ) AS 工单步骤过期3天, 	count( CASE WHEN e.expectendtime <= date_add(now(), INTERVAL - 3 DAY) AND e.expectendtime > date_add(now(), INTERVAL - 10 DAY) THEN 1 END ) AS 工单步骤过期10天, 	count( CASE WHEN e.expectendtime <= date_add(now(), INTERVAL - 10 DAY) AND e.expectendtime > date_add(now(), INTERVAL - 30 DAY) THEN 1 END ) AS 工单步骤过期30天, 	count( CASE WHEN e.expectendtime <= date_add(now(), INTERVAL - 30 DAY) THEN 	1 END ) AS 工单步骤过期30天以上,     e.custlistname, 'B', date_format(e.createddate,'%Y-%m-%d')	 FROM ELITETASK e LEFT JOIN OBJECTIVE o ON e.objective_guid = o.objective_guid WHERE 1 = 1 AND o.createddate < STR_TO_DATE(date_format( date_add( last_day( DATE_ADD(NOW(), INTERVAL - 2 MONTH) ), INTERVAL 1 DAY ), '%Y-%m-%d' ), '%Y-%m-%d %H-%i-%s') GROUP BY e.curstep_id_map, e.MAP_ID, date_format(o.createddate, '%Y-%m-%d'),e.custlistname,e.handleby_id,e.handlegroup_id,date_format(e.createddate,'%Y-%m-%d') ";

			log.debug("插入工单汇总数据B:"+insertSQL3);
			eliteBean.update(insertSQL3,'dataSource');
		}
		catch(Exception e) {
			log.debug("SQL处理异常"+e.getMessage());
		}
	}

	//获取当前是多少分钟
	def static int getMinute(Date date) {
		Calendar calendar = Calendar.getInstance();
		calendar.setTime(date);
		return calendar.get(Calendar.MINUTE);
	}
	//获取当前是多少小时
	def static int getHour(Date date) {
		Calendar calendar = Calendar.getInstance();
		calendar.setTime(date);
		return calendar.get(Calendar.HOUR_OF_DAY);
	}
}